''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：alibaba_keywords.py
#日期：2023-7-15
#备注：阿里巴巴国际站关键词

CREATE TABLE `alibaba_keywords` (
  `keywordId` int(11) COMMENT '关键词id', 
  `kwRaw` varchar(200) COMMENT '关键词名称',
  `groupName` varchar(50) COMMENT '关键词分组名称',
  `matchCategory` varchar(100) COMMENT '最佳匹配类目',
  `pv` int(6) COMMENT '搜索指数',
  `resultCnt` int(6) COMMENT '卖家规模指数',
  `competitionRate` float COMMENT '竞争度=pv/resultCnt,越大越好',
  `clkRate` float COMMENT '搜索点击率，数值越大越好',
  `usedTitleProdCnt` int(6) COMMENT '标题覆盖的产品数',
  `usedKwProdCnt` int(6) COMMENT '关键词覆盖的产品数',
  `selfImpsCnt` int(6) COMMENT '我的搜索曝光次数',
  `selfClkCnt` int(6) COMMENT '我的搜索点击次数',
  `selfClkRate` float COMMENT '我的搜索点击率=selfClkCnt/selfImpsCnt，数值越大越好',
  `top10SlrImpsCnt` int(6) COMMENT '市场Top10平均曝光',
  `top10SlrClkCnt` int(6) COMMENT '市场Top10平均点击',
  `p4pSlrmCnt` int(6) COMMENT '直通车推广中商家数',
  `allCustP4pAvgPrice` float COMMENT '商家平均出价',
  `sameLevelCustP4pAvgPrice` float COMMENT '18.P4P同层平均出价', 
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`kwRaw`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='阿里巴巴关键词信息';

CREATE TABLE `alibaba_keyword_product` (
  `keywordId` int(11) COMMENT '关键词id', 
  `kwRaw` varchar(200) COMMENT '关键词名称',
  `productId` varchar(20) COMMENT '产品id', 
  `groupRemark` varchar(50) COMMENT '产品说明：9D,21D',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`keywordId`,`productId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='阿里巴巴关键词与产品关联信息';
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

from file_deal import replace_txt
from pacong.stock.stock_config import db,cursor
from homework.alibaba.alibaba_base import select_one,select_condition
from pycacho.cachobase.logger import Logger

logger = Logger("alibaba_keywords").get_log()

def database():
    sql1 = 'delete from alibaba_keywords;'
    cursor.execute(sql1)

def get_keywords():
    database()
    file = 'F:\\alibaba\json\\keywords.txt'
    keywords_list = replace_txt(file)
    for keyword in keywords_list:
        #print(keyword)
        keywordId = keyword['keywordId']
        kwRaw = keyword['kwRaw']
        groupName = keyword['groupName']
        matchCategory = keyword['matchCategory']
        pv = keyword['pv']
        resultCnt = keyword['resultCnt']
        competitionRate = keyword['competitionRate']
        clkRate = keyword['clkRate']
        usedTitleProdCnt=keyword['usedTitleProdCnt']
        usedKwProdCnt = keyword['usedKwProdCnt']
        selfImpsCnt = keyword['selfImpsCnt']
        selfClkCnt = keyword['selfClkCnt']
        selfClkRate = keyword['selfClkRate']
        top10SlrImpsCnt = keyword['top10SlrImpsCnt']
        top10SlrClkCnt = keyword['top10SlrClkCnt']
        p4pSlrmCnt = keyword['p4pSlrmCnt']
        allCustP4pAvgPrice = keyword['allCustP4pAvgPrice']
        sameLevelCustP4pAvgPrice = keyword['sameLevelCustP4pAvgPrice']
        insert_keywords(keywordId,kwRaw,groupName,matchCategory,pv,resultCnt,competitionRate,clkRate,
              usedTitleProdCnt,usedKwProdCnt,selfImpsCnt,selfClkCnt,selfClkRate,top10SlrImpsCnt,top10SlrClkCnt,
              p4pSlrmCnt,allCustP4pAvgPrice,sameLevelCustP4pAvgPrice)

# 将阿里巴巴产品分组信息存入到数据库
def insert_keywords(keywordId,kwRaw,groupName,matchCategory,pv,resultCnt,competitionRate,clkRate,
              usedTitleProdCnt,usedKwProdCnt,selfImpsCnt,selfClkCnt,selfClkRate,top10SlrImpsCnt,top10SlrClkCnt,
              p4pSlrmCnt,allCustP4pAvgPrice,sameLevelCustP4pAvgPrice):
    sql = "insert into alibaba_keywords (keywordId,kwRaw,groupName,matchCategory,pv,resultCnt,competitionRate,clkRate," \
          "usedTitleProdCnt,usedKwProdCnt,selfImpsCnt,selfClkCnt,selfClkRate,top10SlrImpsCnt,top10SlrClkCnt," \
          "p4pSlrmCnt,allCustP4pAvgPrice,sameLevelCustP4pAvgPrice,create_time) " \
          "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',now());" \
          % (keywordId,kwRaw,groupName,matchCategory,pv,resultCnt,competitionRate,clkRate,
              usedTitleProdCnt,usedKwProdCnt,selfImpsCnt,selfClkCnt,selfClkRate,top10SlrImpsCnt,top10SlrClkCnt,
              p4pSlrmCnt,allCustP4pAvgPrice,sameLevelCustP4pAvgPrice)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('阿里巴巴关键词 %s 插入成功！', kwRaw)

def get_product_remark():
    keywords = select_one('keywordId','kwRaw', 'alibaba_keywords')
    for keyword in keywords:
        #print(keyword[0])
        remarks = select_condition(keyword[1])
        for remark in remarks:
            #print(keyword[0],keyword[1],remark[0],remark[1])
            insert_keyword_product(keyword[0],keyword[1],remark[0],remark[1])

# 将阿里巴巴产品与关键词关联 存入到数据库
def insert_keyword_product(keywordId, kwRaw,productId,groupRemark):
    sql = "insert into alibaba_keyword_product(keywordId, kwRaw,productId,groupRemark,create_time) " \
          "values ('%s','%s','%s','%s',now());" \
          % (keywordId, kwRaw,productId,groupRemark)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()
        logger.info('%s阿里巴巴产品关键词关联信息插入成功！', kwRaw+'-'+groupRemark)


if __name__ == '__main__':
    #get_keywords()
    get_product_remark()